package edu.nf.utils;

import java.sql.*;

public class SQLExecutor {

    private Connection connection;

    private boolean autoClose = true;

    public SQLExecutor(Connection connection) {
        this.connection = connection;
    }

    /**
     * 开启事务
     */
    public void beginTransaction(){
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            throw new RuntimeException("开启事务时发生异常！",e);
        }
        autoClose = false;
    }

    /**
     * 提交事务
     */
    public void commit() {
        try {
            if (!connection.getAutoCommit()) {
                connection.commit();
            }
        }catch (SQLException e){
            throw new RuntimeException("提交事务时发生了异常！",e);
        }finally {
            close();
        }
    }

    /**
     * 回滚事务
     */
    public void rollback(){
        try {
            connection.rollback();
            close();
        } catch (SQLException e) {
            throw new RuntimeException("回滚事务时发生了异常！");
        }
    }


    public <T> T executeQuery(String sql,ResultSetHandler<T> handler , Object... args) throws SQLException{

        if (connection == null) {
            throw new SQLException("Null connection.");
        }

        if (sql == null) {
            close();
            throw new SQLException("Null SQL statement");
        }

        if(handler == null){
            close();
            throw new SQLException("Null ResultSetHandler");
        }

        PreparedStatement ps = connection.prepareStatement(sql);
        setParams(args,ps);
        ResultSet rs = ps.executeQuery();
        T t = handler.handle(rs);
        close(rs);
        close(ps);
        if (autoClose) {
            close();
        }
        return t;
    }

    /**
     * 执行DML语句
     *
     * @param sql
     * @param args
     * @return
     */
    public int executeUpdate(String sql, Object... args) throws SQLException {

        if (connection == null) {
            throw new SQLException("Null connection.");
        }

        if (sql == null) {
            throw new SQLException("Null SQL statement.");
        }


        PreparedStatement ps = connection.prepareStatement(sql);

        setParams(args, ps);

        int row = ps.executeUpdate();
        close(ps);
        if (autoClose) {
            close();
        }

        return row;
    }

    /**
     * 执行批量操作
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public int[] executeBatch(String sql,Object[][] params) throws SQLException{

        if (connection == null) {
            throw new SQLException("Null connection.");
        }

        if (sql == null) {
            throw new SQLException("Null SQL statement.");
        }

        PreparedStatement ps = connection.prepareStatement(sql);

        for (int i = 0; i < params.length; i++) {
            setParams(params[i],ps);
            ps.addBatch();
        }

        int[] row = ps.executeBatch();
        //检查是否所有都执行成功了。
        try {
            for (int rs : row) {
                if(rs == 0){
                    throw new SQLException("修改失败");
                }
            }
        } finally {
            close(ps);
            if (autoClose) {
                close();
            }
        }
        return row;
    }

    /**
     * 替换参数
     */
    private void setParams(Object[] params, PreparedStatement ps) {
        try {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
        } catch (SQLException e) {
            throw new RuntimeException("Exception in setParams.");
        }
    }

    /**
     * 关闭结果集
     *
     * @param rs
     */
    private void close(ResultSet rs){
        try {
            rs.close();
        } catch (SQLException e) {
            throw new RuntimeException("关闭资源时发生了异常！",e);
        }
    }

    /**
     * 关闭语句对象
     *
     * @param st
     */
    private void close(Statement st){
        try {
            st.close();
        } catch (SQLException e) {
            throw new RuntimeException("关闭资源时发生了异常！",e);
        }
    }

    /**
     * 关闭连接
     */
    private void close(){
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException("关闭资源时发生了异常！",e);
        }
    }

}
